﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Skyworth.CRM.DAL
{
    public class WeekLongDAL : SuperDataBase, IWeekLongDAL
    {
        public void Add(WeekLong wldm,WeekLongEntry ewldm)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" insert into T_Sales_WeekLong(FNumber,Fdate,FBeginDate,FEndinDate,FMaker,FPricelistID) ");
                strSql.Append(" values( ");
                strSql.Append("@FNumber,@Fdate,@FBeginDate,@FEndinDate,@FMaker,@FPricelistID)");
                strSql.Append(";select @NewID=@@IDENTITY");
                using (TransactionScope ts = new TransactionScope())
                {
                    DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                    DB.AddInParameter(cmd, "@FNumber", DbType.String, wldm.FNumber);
                    DB.AddInParameter(cmd, "@Fdate", DbType.Date, wldm.Fdate);
                    DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, wldm.FBeginDate);
                    DB.AddInParameter(cmd, "@FEndinDate", DbType.Date, wldm.FEndinDate);
                    DB.AddInParameter(cmd, "@FMaker", DbType.Int32, wldm.FMaker);
                    DB.AddInParameter(cmd, "@FPricelistID", DbType.Int32, wldm.FPricelistID);
                    DB.AddOutParameter(cmd, "@NewID", DbType.Int32, 32);
                    DB.ExecuteNonQuery(cmd);
                    int custId = Convert.ToInt32(DB.GetParameterValue(cmd, "@NewID"));
                    // 调价明细
                    StringBuilder sqlItem = new StringBuilder();
                    sqlItem.Append("insert into T_Sales_WeekLongEntry(FWeekLongID,FEntryID,FOLDPrice,FNEWPrice,FProductid,FTaxNewPrice)");
                    sqlItem.Append(" values (");
                    sqlItem.Append("@FCustId,@FEntryID,@FOLDPrice,@FNEWPrice,@FProductid,@FTaxNewPrice)");
                    cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                    DB.AddInParameter(cmd, "@FCustId", DbType.Int32, custId);
                    DB.AddInParameter(cmd, "@FEntryID", DbType.Int32, ewldm.FEntryID);
                    DB.AddInParameter(cmd, "@FOLDPrice", DbType.Decimal, ewldm.FOLDPrice);
                    DB.AddInParameter(cmd, "@FNEWPrice", DbType.Decimal, ewldm.FNEWPrice);
                    DB.AddInParameter(cmd, "@FProductid", DbType.Int32, ewldm.FProductid);
                    DB.AddInParameter(cmd, "@FTaxNewPrice", DbType.Decimal, ewldm.FTaxNewPrice);
                    DB.ExecuteNonQuery(cmd);

                    // 更新新价格
                    StringBuilder sqlupdate = new StringBuilder();
                    sqlupdate.Append(" update T_Sales_PriceList set Price=@FNEWPrice,StartDate=@FBeginDate,EndDate=@FEndinDate,TaxPrice=@TaxPrice where Id=@FPricelistID");
                    cmd = DB.GetSqlStringCommand(sqlupdate.ToString());
                    DB.AddInParameter(cmd, "@FNEWPrice", DbType.Decimal, ewldm.FNEWPrice);
                    DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, wldm.FBeginDate);
                    DB.AddInParameter(cmd, "@FEndinDate", DbType.Date, wldm.FEndinDate);
                    DB.AddInParameter(cmd, "@TaxPrice", DbType.Decimal, ewldm.FTaxNewPrice);
                    DB.AddInParameter(cmd, "@FPricelistID", DbType.Int32, wldm.FPricelistID);
                    DB.ExecuteNonQuery(cmd);

                    ts.Complete();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


       public DataTable GetWeekLongByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum,a.FNumber,a.FMaker,convert(varchar(10),a.Fdate,121) Fdate,convert(varchar(10),a.FBeginDate,121) FBeginDate,convert(varchar(10),a.FEndinDate,121) FEndinDate,a.FPricelistID,c.SchemeCode,c.SchemeName,", strOrderBy);
           strCommandText.Append(" b.FOLDPrice,b.FNEWPrice,b.FProductid,p.ProductCode,p.ProductName ");
           strCommandText.Append(" from T_Sales_WeekLong a inner join T_Sales_WeekLongEntry b on a.Fitemid=b.FWeekLongID ");
           strCommandText.Append(" inner join T_BasicData_Product p on p.ProductId=b.FProductid ");
           strCommandText.Append(" inner join T_Sales_PriceList c on a.FPricelistID=c.Id ");
           strCommandText.Append(" inner join T_Sales_PricePolicy d on d.PolicyId=c.PolicyId");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public void InsertCredit(Int32 FCreditType, Int32 Fcustid, Int32 Ftype, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FCreditWay, decimal FYearAmount, Int32 FisYes)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" insert into T_Credit(FCreditType,Fcustid,Ftype,FBeginDate,FEndDate,Famount,FCreditWay,FYearAmount,FisYes) ");
               strSql.Append(" values( ");
               strSql.Append("@FCreditType,@Fcustid,@Ftype,@FBeginDate,@FEndDate,@Famount,@FCreditWay,@FYearAmount,@FisYes)");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.AddInParameter(cmd, "@FCreditType", DbType.Int32, FCreditType);
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@Ftype", DbType.Int32, Ftype);
                   DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, FBeginDate);
                   DB.AddInParameter(cmd, "@FEndDate", DbType.Date, FEndDate);
                   DB.AddInParameter(cmd, "@Famount", DbType.Decimal, Famount);
                   DB.AddInParameter(cmd, "@FCreditWay", DbType.Int32, FCreditWay);
                   DB.AddInParameter(cmd, "@FYearAmount", DbType.Decimal, FYearAmount);
                   DB.AddInParameter(cmd, "@FisYes", DbType.Int32, FisYes);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   if (Ftype == 1)
                   {
                       sqlItem.Append(" set FcreditAmount=FcreditAmount+" + Famount);
                       sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
                   }
                   else
                   {
                       sqlItem.Append(" set FcreditAmount=FcreditAmount-" + Famount);
                       sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount-" + Famount);
                       //sqlItem.Append(" set FcreditAmount=" + Famount);
                       //sqlItem.Append("  ,FTotalAmount=FCashAmount+" + Famount);
                   }
                   sqlItem.Append(" where Fcustid=" + Fcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder strSqlCashEntry = new StringBuilder();
                   strSqlCashEntry.Append(" insert into dbo.T_CashEntry(Fbillno,Ftype,Fcustid,FAmount,Fcashtype,Fdate) ");
                   strSqlCashEntry.Append(" values( ");
                   strSqlCashEntry.Append("@Fbillno,2,@Fcustid,@FAmount,0,getdate())");
                   cmd = DB.GetSqlStringCommand(strSqlCashEntry.ToString());
                   DB.AddInParameter(cmd, "@Fbillno", DbType.String,  DateTime.Now.ToString("yyyymmddhhmmss"));
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   if (Ftype == 1)
                   {
                       DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, Famount);
                   }
                   else
                   {
                       DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, -Famount);
                   }
                   DB.AddOutParameter(cmd, "@Fcashtype", DbType.Int32, 0);
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }



       //public void UpdateCredit(Int32 Fitemid, Int32 FCreditType, Int32 Fcustid, Int32 Ftype, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FCreditWay, decimal FYearAmount, Int32 FisYes)
       //{
       //    try
       //    {
       //        StringBuilder strSql = new StringBuilder();
       //        strSql.Append("   UPDATE T_Credit set ");
       //        strSql.Append(" FCreditType=" + FCreditType);
       //        strSql.Append(" ,Ftype=" + Ftype);
       //        strSql.Append(" ,FBeginDate=@FBeginDate");
       //        strSql.Append(" ,FEndDate=@FEndDate");
       //        strSql.Append(" ,Famount=" + Famount);
       //        strSql.Append(" ,FisYes=" + FisYes);
       //        strSql.Append(" where Fitemid=" + Fitemid);
       //        using (TransactionScope ts = new TransactionScope())
       //        {
       //            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
       //            DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, FBeginDate);
       //            DB.AddInParameter(cmd, "@FEndDate", DbType.Date, FEndDate);
       //            DB.ExecuteNonQuery(cmd);

       //            StringBuilder sqlItem = new StringBuilder();
       //            sqlItem.Append(" update T_Cash ");
       //            if (Ftype == 1)
       //            {
       //                sqlItem.Append(" set FcreditAmount=FcreditAmount+" + Famount);
       //                sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
       //            }
       //            else
       //            {
       //                sqlItem.Append(" set FcreditAmount=" + Famount);
       //                sqlItem.Append("  ,FTotalAmount=FCashAmount+" + Famount);
       //            }
       //            sqlItem.Append(" where Fcustid=" + Fcustid);
       //            cmd = DB.GetSqlStringCommand(sqlItem.ToString());
       //            DB.ExecuteNonQuery(cmd);
       //            ts.Complete();
       //        }
       //    }
       //    catch (Exception ex)
       //    {
       //        throw ex;
       //    }
       //}





       public DataTable GetCreditByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum, t.* ", strOrderBy);
           strCommandText.Append("from ( ");
           strCommandText.Append("SELECT a.Fitemid,a.FCreditType,a.Fcustid,a.Ftype,convert(varchar(10),a.FBeginDate,121) FBeginDate,convert(varchar(10),a.FEndDate,121) FEndDate,a.Famount,a.FCreditWay,a.FYearAmount,a.FisYes,b.FName,b.FNumber, ");
           strCommandText.Append(" case FCreditType when 1 then '销售信用' when 2 then '财务信用' when 3 then '业务员信用' else '' end FCreditTypeName,");
           strCommandText.Append("case Ftype when 1 then '追加信用额度' when 2 then '扣减信用额度' else '' end FtypeName,");
           strCommandText.Append("case FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName, ");
           strCommandText.Append("case FCreditWay when 1 then '按比率授信' else '' end FCreditWayName ");
           strCommandText.Append(" from T_Credit a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid ");
           strCommandText.Append(" ) t ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public DataTable QueryCredit(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("select a.Fitemid,a.FCreditType,a.Fcustid,a.Ftype,a.FBeginDate,a.FEndDate,a.Famount,a.FCreditWay,a.FYearAmount,a.FisYes,b.FName,b.FNumber,");
               strSql.Append(" case FCreditType when 1 then '销售信用' when 2 then '财务信用' when 3 then '业务员信用' else '' end FCreditTypeName,");
               strSql.Append("case Ftype when 1 then '追加信用额度' when 2 then '扣减信用额度' else '' end FtypeName,");
               strSql.Append("case FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName ");
               strSql.Append(" from T_Credit a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid  where 1=1 ");

               if (strWhere.Trim() != "")
               {
                   strSql.Append(strWhere);
               }
               DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public DataTable QueryCash(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("select Fitemid,Fcustid,FCashAmount,FcreditAmount,FTotalAmount from T_Cash where 1=1 " );
               if (strWhere.Trim() != "")
               {
                   strSql.Append(strWhere);
               }
               DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }


       //void InsertMoney(Int32 FmoneyType, Int32 Fcustid, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FisYes);
       //void UpdateMoney(Int32 Fitemid, Int32 FmoneyType, Int32 Fcustid, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FisYes);

       //DataTable GetMoneyByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize,
       //                                    out int recordCount, out int pageCount);
       //DataTable QueryMoney(string strWhere);


       public void InsertMoney(Int32 FmoneyType, Int32 Fcustid, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FisYes,string Fbillno,Int32 Ftype,Int32 Fcashtype)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" insert into T_Readymoney(FmoneyType,Fcustid,FBeginDate,FEndDate,Famount,FisYes) ");
               strSql.Append(" values( ");
               strSql.Append("@FmoneyType,@Fcustid,@FBeginDate,@FEndDate,@Famount,@FisYes)");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.AddInParameter(cmd, "@FmoneyType", DbType.Int32, FmoneyType);
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, FBeginDate);
                   DB.AddInParameter(cmd, "@FEndDate", DbType.Date, FEndDate);
                   DB.AddInParameter(cmd, "@Famount", DbType.Decimal, Famount);
                   DB.AddInParameter(cmd, "@FisYes", DbType.Int32, FisYes);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlMoney = new StringBuilder();
                   sqlMoney.Append(" insert into T_CashEntry(Fbillno,Ftype,Fcustid,FAmount,Fcashtype,Fdate) ");
                   sqlMoney.Append(" values( ");
                   sqlMoney.Append("@Fbillno,@Ftype,@Fcustid,@FAmount,@Fcashtype,getdate())");
                   cmd = DB.GetSqlStringCommand(sqlMoney.ToString());
                   DB.AddInParameter(cmd, "@Fbillno", DbType.String, Fbillno);
                   DB.AddInParameter(cmd, "@Ftype", DbType.Int32, Ftype);
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, Famount);
                   DB.AddInParameter(cmd, "@Fcashtype", DbType.Int32, Fcashtype);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount+" + Famount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
                   sqlItem.Append(" where Fcustid=" + Fcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }



       //public void UpdateMoney(Int32 Fitemid, Int32 FmoneyType, Int32 Fcustid, DateTime FBeginDate, DateTime FEndDate, decimal Famount, Int32 FisYes, string Fbillno, Int32 Ftype, Int32 Fcashtype)
       //{
       //    try
       //    {
       //        StringBuilder strSql = new StringBuilder();
       //        strSql.Append("   UPDATE T_Readymoney set ");
       //        strSql.Append(" FmoneyType=" + FmoneyType);
       //        strSql.Append(" ,FBeginDate=@FBeginDate");
       //        strSql.Append(" ,FEndDate=@FEndDate");
       //        strSql.Append(" ,Famount=" + Famount);
       //        strSql.Append(" ,FisYes=" + FisYes);
       //        strSql.Append(" where Fitemid=" + Fitemid);
       //        using (TransactionScope ts = new TransactionScope())
       //        {
       //            DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
       //            DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, FBeginDate);
       //            DB.AddInParameter(cmd, "@FEndDate", DbType.Date, FEndDate);
       //            DB.ExecuteNonQuery(cmd);


       //            StringBuilder sqlMoney = new StringBuilder();
       //            sqlMoney.Append("   UPDATE T_CashEntry set ");
       //            sqlMoney.Append(" Ftype=" + Ftype);
       //            sqlMoney.Append(" ,Famount=" + Famount);
       //            sqlMoney.Append(" ,Fcashtype=" + Fcashtype);
       //            sqlMoney.Append(" where Fitemid=" + Fitemid);
       //            cmd = DB.GetSqlStringCommand(sqlMoney.ToString());
       //            DB.AddInParameter(cmd, "@Fbillno", DbType.String, Fbillno);
       //            DB.AddInParameter(cmd, "@Ftype", DbType.Int32, Ftype);
       //            DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
       //            DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, Famount);
       //            DB.AddInParameter(cmd, "@Fcashtype", DbType.Int32, Fcashtype);
       //            DB.ExecuteNonQuery(cmd);


       //            StringBuilder sqlItem = new StringBuilder();
       //            sqlItem.Append(" update T_Cash ");
       //            sqlItem.Append(" set FCashAmount=FCashAmount+" + Famount);
       //            sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
       //            sqlItem.Append(" where Fcustid=" + Fcustid);
       //            cmd = DB.GetSqlStringCommand(sqlItem.ToString());
       //            DB.ExecuteNonQuery(cmd);
       //            ts.Complete();
       //        }
       //    }
       //    catch (Exception ex)
       //    {
       //        throw ex;
       //    }
       //}





       public DataTable GetMoneyByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum, t.* ", strOrderBy);
           strCommandText.Append("from ( ");
           strCommandText.Append("SELECT a.Fitemid,a.FmoneyType,a.Fcustid,convert(varchar(10),a.FBeginDate,121) FBeginDate,convert(varchar(10),a.FEndDate,121) FEndDate,a.Famount,a.FisYes,b.FName,b.FNumber, ");
           strCommandText.Append(" case FmoneyType when 1 then '支票' when 2 then '现金' when 3 then '银行汇款' when 4 then '转账' else '' end FmoneyTypeName,");
           strCommandText.Append("case FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName ");
           strCommandText.Append(" from T_Readymoney a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid ");
           strCommandText.Append(" ) t ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public DataTable QueryMoney(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("SELECT a.Fitemid,a.FmoneyType,a.Fcustid,a.FBeginDate,a.FEndDate,a.Famount,a.FisYes,b.FName,b.FNumber, ");
               strSql.Append(" case FmoneyType when 1 then '支票' when 2 then '现金' when 3 then '银行汇款' when 4 then '转账' else '' end FmoneyTypeName,");
               strSql.Append("case FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName ");
               strSql.Append(" from T_Readymoney a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid  where 1=1 ");
  
               if (strWhere.Trim() != "")
               {
                   strSql.Append(strWhere);
               }
               DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public void AddSpecial(Special wldm, SpecialEntry ewldm)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" insert into T_Sales_Special(FNumber,Fdate,FBeginDate,FEndinDate,FMaker,FPricelistID) ");
               strSql.Append(" values( ");
               strSql.Append("@FNumber,@Fdate,@FBeginDate,@FEndinDate,@FMaker,@FPricelistID)");
               strSql.Append(";select @NewID=@@IDENTITY");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.AddInParameter(cmd, "@FNumber", DbType.String, wldm.FNumber);
                   DB.AddInParameter(cmd, "@Fdate", DbType.Date, wldm.Fdate);
                   DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, wldm.FBeginDate);
                   DB.AddInParameter(cmd, "@FEndinDate", DbType.Date, wldm.FEndinDate);
                   DB.AddInParameter(cmd, "@FMaker", DbType.Int32, wldm.FMaker);
                   DB.AddInParameter(cmd, "@FPricelistID", DbType.Int32, wldm.FPricelistID);
                   DB.AddOutParameter(cmd, "@NewID", DbType.Int32, 32);
                   DB.ExecuteNonQuery(cmd);
                   int custId = Convert.ToInt32(DB.GetParameterValue(cmd, "@NewID"));
                   // 调价明细
                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append("insert into T_Sales_SpecialEntry(SpecialID,FEntryID,FOLDPrice,FNEWPrice,FProductid,FisSet,FSetQty,FTaxNewPrice)");
                   sqlItem.Append(" values (");
                   sqlItem.Append("@FCustId,@FEntryID,@FOLDPrice,@FNEWPrice,@FProductid,@FisSet,@FSetQty,@FTaxNewPrice)");
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.AddInParameter(cmd, "@FCustId", DbType.Int32, custId);
                   DB.AddInParameter(cmd, "@FEntryID", DbType.Int32, ewldm.FEntryID);
                   DB.AddInParameter(cmd, "@FOLDPrice", DbType.Decimal, ewldm.FOLDPrice);
                   DB.AddInParameter(cmd, "@FNEWPrice", DbType.Decimal, ewldm.FNEWPrice);
                   DB.AddInParameter(cmd, "@FProductid", DbType.Int32, ewldm.FProductid);
                   DB.AddInParameter(cmd, "@FisSet", DbType.Int32, ewldm.FisSet);
                   DB.AddInParameter(cmd, "@FSetQty", DbType.Int32, ewldm.FSetQty);
                   DB.AddInParameter(cmd, "@FTaxNewPrice", DbType.Decimal, ewldm.FTaxNewPrice);
                   DB.ExecuteNonQuery(cmd);

                   // 更新新价格
                   StringBuilder sqlupdate = new StringBuilder();
                   sqlupdate.Append(" update T_Sales_PriceList set Price=@FNEWPrice,StartDate=@FBeginDate,EndDate=@FEndinDate,TaxPrice=@TaxPrice where Id=@FPricelistID");
                   cmd = DB.GetSqlStringCommand(sqlupdate.ToString());
                   DB.AddInParameter(cmd, "@FNEWPrice", DbType.Decimal, ewldm.FNEWPrice);
                   DB.AddInParameter(cmd, "@FBeginDate", DbType.Date, wldm.FBeginDate);
                   DB.AddInParameter(cmd, "@FEndinDate", DbType.Date, wldm.FEndinDate);
                   DB.AddInParameter(cmd, "@TaxPrice", DbType.Decimal, ewldm.FTaxNewPrice);
                   DB.AddInParameter(cmd, "@FPricelistID", DbType.Int32, wldm.FPricelistID);
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public DataTable GetSpecialByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum,a.FNumber,a.FMaker,convert(varchar(10),a.Fdate,121) Fdate,convert(varchar(10),a.FBeginDate,121) FBeginDate,convert(varchar(10),a.FEndinDate,121) FEndinDate,a.FPricelistID,c.SchemeCode,c.SchemeName,", strOrderBy);
           strCommandText.Append(" b.FOLDPrice,b.FNEWPrice,b.FProductid,p.ProductCode,p.ProductName ");
           strCommandText.Append(" ,b.FisSet,case b.FisSet when 1 then '限量' when 0 then '不限量' else '' end as FisSetName,b.FSetQty ");
           strCommandText.Append(" from T_Sales_Special a inner join T_Sales_SpecialEntry b on a.Fitemid=b.SpecialID ");
           strCommandText.Append(" inner join T_BasicData_Product p on p.ProductId=b.FProductid ");
           strCommandText.Append(" inner join T_Sales_PriceList c on a.FPricelistID=c.Id ");
           strCommandText.Append(" inner join T_Sales_PricePolicy d on d.PolicyId=c.PolicyId");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0]; 
       }


       public void DeleteCredit(int nFitemid, int nFcustid, int nFtype, decimal deFamount)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("delete from T_Credit ");
               strSql.Append(" where Fitemid=" + nFitemid + "");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   if (nFtype == 1)
                   {
                       sqlItem.Append(" set FcreditAmount=FcreditAmount-" + deFamount);
                       sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount-" + deFamount);
                   }
                   else
                   {
                       sqlItem.Append(" set FcreditAmount=FcreditAmount+" + deFamount);
                       sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + deFamount);
                   }
                   sqlItem.Append(" where Fcustid=" + nFcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }


       public void DeleteMoney(int nFitemid, int nFcustid,decimal deFamount)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("delete from T_Readymoney ");
               strSql.Append(" where Fitemid=" + nFitemid + "");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount-" + deFamount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount-" + deFamount);
                   sqlItem.Append(" where Fcustid=" + nFcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public DataTable QueryCustomerMoney(string strWhere)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" select a.Fitemid,a.Fcustid,a.FCashAmount,a.FcreditAmount,a.FTotalAmount,b.FName,b.FNumber,b.FDree,b.FMobil,b.FTel,b.FQH,b.FJB,b.FDreeId,b.FSalesId ");
               strSql.Append(" from T_Cash a inner join T_BasicData_Customer b on a.Fcustid=b.FItemId  where 1=1 ");

               if (strWhere.Trim() != "")
               {
                   strSql.Append(strWhere);
               }
               DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public DataTable GetCustomerMoneyByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum,a.Fitemid,a.Fcustid,a.FCashAmount,a.FcreditAmount,a.FTotalAmount,b.FName,b.FNumber,b.FDree,b.FMobil,b.FTel,b.FQH,b.FJB,b.FDreeId,b.FSalesId ", strOrderBy);
           strCommandText.Append(" from T_Cash a inner join T_BasicData_Customer b on a.Fcustid=b.FItemId  ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

       public DataTable GetCustomerMoneyItemByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum,a.Fitemid,a.Fcustid,a.Fbillno,a.Ftype,a.Fcashtype,a.FAmount,convert(varchar(10),a.Fdate,121) Fdate ,b.FName,b.FNumber,b.FDree,b.FMobil,b.FTel,b.FQH,b.FJB,b.FDreeId,b.FSalesId, ", strOrderBy);
           strCommandText.Append(" case a.Ftype when 1 then '现款'  when 2 then '信用额度' else '' end FtypeName,  ");
           strCommandText.Append(" case a.Fcashtype when 1 then '手工增加' when 2 then '订单耗用' when 3 then '返利入款' else '' end FcashtypeName ");
           strCommandText.Append(" from T_CashEntry a inner join T_BasicData_Customer b on a.Fcustid=b.FItemId ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }


       public void UpdateMoney(int nFitemid, decimal deFCashAmount, decimal deFcreditAmount)
       {
           try
           {

               StringBuilder sqlMoney = new StringBuilder();
               sqlMoney.Append(" update T_Cash ");
               sqlMoney.Append(" set FCashAmount=" + deFCashAmount);
               sqlMoney.Append("  ,FcreditAmount=" + deFcreditAmount);
               sqlMoney.Append("  ,FTotalAmount=" + deFCashAmount + "+" + deFcreditAmount);
               sqlMoney.Append(" where Fitemid=" + nFitemid);
               DbCommand cmd = DB.GetSqlStringCommand(sqlMoney.ToString());
               DB.ExecuteNonQuery(cmd);

           }
           catch (Exception ex)
           {
               throw ex;
           }
       }


       public DataTable GetReceivablesByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum, t.* ", strOrderBy);
           strCommandText.Append("from ( ");
           strCommandText.Append("SELECT a.Fitemid,a.Fcustid,convert(varchar(10),a.FReceDate,121) FReceDate,a.Fnode,a.FHuiNumber,a.FcustType,a.FReceivablesType,a.Famount,a.FisYes,b.FName,b.FNumber, ");
           strCommandText.Append(" case FcustType when 1 then '大客户部' when 2 then '渠道部'  else '' end FcustTypeName,");
           strCommandText.Append(" case FReceivablesType when 1 then '现金' when 2 then '电汇' when 3 then '承兑'  else '' end FReceivablesTypeName,");
           strCommandText.Append("case FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName ");
           strCommandText.Append(" from T_Receivables a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid ");
           strCommandText.Append(" ) t ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }


       public void InsertReceivables(Int32 FcustType, Int32 Fcustid, DateTime FReceDate, string Fnode, decimal Famount, Int32 FReceivablesType, string FHuiNumber, Int32 FisYes)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" insert into T_Receivables(FcustType,Fcustid,FReceDate,Fnode,Famount,FReceivablesType,FHuiNumber,FisYes) ");
               strSql.Append(" values( ");
               strSql.Append("@FcustType,@Fcustid,@FReceDate,@Fnode,@Famount,@FReceivablesType,@FHuiNumber,@FisYes)");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.AddInParameter(cmd, "@FcustType", DbType.Int32, FcustType);
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FReceDate", DbType.Date, FReceDate);
                   DB.AddInParameter(cmd, "@Fnode", DbType.String, Fnode);
                   DB.AddInParameter(cmd, "@Famount", DbType.Decimal, Famount);
                   DB.AddInParameter(cmd, "@FReceivablesType", DbType.Int32, FReceivablesType);
                   DB.AddInParameter(cmd, "@FHuiNumber", DbType.String, FHuiNumber);
                   DB.AddInParameter(cmd, "@FisYes", DbType.Int32, FisYes);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlMoney = new StringBuilder();
                   sqlMoney.Append(" insert into T_CashEntry(Fbillno,Ftype,Fcustid,FAmount,Fcashtype,Fdate) ");
                   sqlMoney.Append(" values( ");
                   sqlMoney.Append("@Fbillno,1,@Fcustid,@FAmount,5,getdate())");
                   cmd = DB.GetSqlStringCommand(sqlMoney.ToString());
                   DB.AddInParameter(cmd, "@Fbillno", DbType.String,  DateTime.Now.ToString("yyyymmddhhmmss"));
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, Famount);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount+" + Famount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
                   sqlItem.Append(" where Fcustid=" + Fcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }


       public void DeleteReceivables(int nFitemid, int nFcustid, decimal deFamount)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("delete from T_Receivables ");
               strSql.Append(" where Fitemid=" + nFitemid + "");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount-" + deFamount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount-" + deFamount);
                   sqlItem.Append(" where Fcustid=" + nFcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       //返利单
       public DataTable GetRebateByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat("SELECT row_number() over({0}) AS rownum, t.* ", strOrderBy);
           strCommandText.Append("from ( ");
           strCommandText.Append("SELECT a.Fitemid,a.Fcustid,convert(varchar(10),a.FRebateDate,121) FRebateDate,a.Fnode,a.FcustType,a.Famount,a.FisYes,b.FName, ");
           strCommandText.Append(" case a.FcustType when 1 then '大客户部' when 2 then '渠道部'  else '' end FcustTypeName,");
           strCommandText.Append(" case a.FisYes when 1 then '生效' when 0 then '不生效' else '' end FisYesName ");
           strCommandText.Append(" from T_Rebate a inner join T_BasicData_Customer b on a.Fcustid=b.Fitemid ");
           strCommandText.Append(" ) t ");
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }

        //返利单
       public void InsertRebate(Int32 FcustType, Int32 Fcustid, DateTime FRebateDate, string Fnode, decimal Famount, Int32 FisYes)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append(" insert into T_Rebate(FcustType,Fcustid,FRebateDate,Fnode,Famount,FisYes) ");
               strSql.Append(" values( ");
               strSql.Append("@FcustType,@Fcustid,@FRebateDate,@Fnode,@Famount,@FisYes)");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.AddInParameter(cmd, "@FcustType", DbType.Int32, FcustType);
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FRebateDate", DbType.Date, FRebateDate);
                   DB.AddInParameter(cmd, "@Fnode", DbType.String, Fnode);
                   DB.AddInParameter(cmd, "@Famount", DbType.Decimal, Famount);
                   DB.AddInParameter(cmd, "@FisYes", DbType.Int32, FisYes);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlMoney = new StringBuilder();
                   sqlMoney.Append(" insert into T_CashEntry(Fbillno,Ftype,Fcustid,FAmount,Fcashtype,Fdate) ");
                   sqlMoney.Append(" values( ");
                   sqlMoney.Append("@Fbillno,1,@Fcustid,@FAmount,3,getdate())");
                   cmd = DB.GetSqlStringCommand(sqlMoney.ToString());
                   DB.AddInParameter(cmd, "@Fbillno", DbType.String, DateTime.Now.ToString("yyyymmddhhmmss"));
                   DB.AddInParameter(cmd, "@Fcustid", DbType.Int32, Fcustid);
                   DB.AddInParameter(cmd, "@FAmount", DbType.Decimal, Famount);
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount+" + Famount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount+" + Famount);
                   sqlItem.Append(" where Fcustid=" + Fcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       //返利单
       public void DeleteRebate(int nFitemid, int nFcustid, decimal deFamount)
       {
           try
           {
               StringBuilder strSql = new StringBuilder();
               strSql.Append("delete from T_Rebate ");
               strSql.Append(" where Fitemid=" + nFitemid + "");
               using (TransactionScope ts = new TransactionScope())
               {
                   DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                   DB.ExecuteNonQuery(cmd);

                   StringBuilder sqlItem = new StringBuilder();
                   sqlItem.Append(" update T_Cash ");
                   sqlItem.Append(" set FCashAmount=FCashAmount-" + deFamount);
                   sqlItem.Append("  ,FTotalAmount=FCashAmount+FcreditAmount-" + deFamount);
                   sqlItem.Append(" where Fcustid=" + nFcustid);
                   cmd = DB.GetSqlStringCommand(sqlItem.ToString());
                   DB.ExecuteNonQuery(cmd);

                   ts.Complete();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }


    }
}
